﻿
namespace SistemaLogistica.DataAccess
{
    using System.Collections.Generic;
    using System.Data;
    using System.Data.Common;
    using SistemaLogistica.Business.Entities;

    public class AreaData : BaseDataAccess
    {
        private AreaData() { }

        private readonly static AreaData instancia = new AreaData();

        public static AreaData Instancia
        {
            get { return instancia; }
        }

        public List<Area> Listar()
        {
            List<Area> areas = new List<Area>();
            DbCommand command = db.GetStoredProcCommand("GetAllFromArea");

            using (IDataReader dr = db.ExecuteReader(command))
            {
                while (dr.Read())
                {
                    areas.Add(new Area
                    {
                        Descripcion = dr.GetString(dr.GetOrdinal("Descripcion")),
                        IdArea = dr.GetInt32(dr.GetOrdinal("IdArea")),
                        IdAreaSuperior = dr.IsDBNull(dr.GetOrdinal("IdAreaSuperior")) ? byte.MinValue : dr.GetInt32(dr.GetOrdinal("IdAreaSuperior")),
                        Nombre = dr.GetString(dr.GetOrdinal("Nombre")),
                        Sucursal = SucursalData.Instancia.ObtenerPorID(dr.GetInt32(dr.GetOrdinal("IdSucursal")))
                    });
                }
            }
            return areas;
        }

        public List<Area> Buscar(string nombre)
        {
            List<Area> areas = new List<Area>();
            DbCommand command = db.GetStoredProcCommand("SearchAreas");
            db.AddInParameter(command, "nombre", DbType.String, nombre);

            using (IDataReader dr = db.ExecuteReader(command))
            {
                while (dr.Read())
                {
                    areas.Add(new Area
                    {
                        Descripcion = dr.GetString(dr.GetOrdinal("Descripcion")),
                        IdArea = dr.GetInt32(dr.GetOrdinal("IdArea")),
                        IdAreaSuperior = dr.IsDBNull(dr.GetOrdinal("IdAreaSuperior")) ? byte.MinValue : dr.GetInt32(dr.GetOrdinal("IdAreaSuperior")),
                        Nombre = dr.GetString(dr.GetOrdinal("Nombre")),
                        Sucursal = SucursalData.Instancia.ObtenerPorID(dr.GetInt32(dr.GetOrdinal("IdSucursal")))
                    });
                }
            }
            return areas;
        }

        public List<Area> ListarPorAreaSuperior(int areaSuperiorID)
        {
            List<Area> areas = new List<Area>();
            DbCommand command = db.GetStoredProcCommand("GetAreasByAreaSuperiorID");
            db.AddInParameter(command, "idAreaSuperior", DbType.Int32, areaSuperiorID);

            using (IDataReader dr = db.ExecuteReader(command))
            {
                while (dr.Read())
                {
                    areas.Add(new Area
                    {
                        Descripcion = dr.GetString(dr.GetOrdinal("Descripcion")),
                        IdArea = dr.GetInt32(dr.GetOrdinal("IdArea")),
                        IdAreaSuperior = dr.IsDBNull(dr.GetOrdinal("IdAreaSuperior")) ? byte.MinValue : dr.GetInt32(dr.GetOrdinal("IdAreaSuperior")),
                        Nombre = dr.GetString(dr.GetOrdinal("Nombre")),
                        Sucursal = SucursalData.Instancia.ObtenerPorID(dr.GetInt32(dr.GetOrdinal("IdSucursal")))
                    });
                }
            }
            return areas;
        }

        public List<Area> ListarPorSucursal(int sucursalID)
        {
            List<Area> areas = new List<Area>();
            DbCommand command = db.GetStoredProcCommand("GetAreasBySucursalID");
            db.AddInParameter(command, "idSucursal", DbType.Int32, sucursalID);

            using (IDataReader dr = db.ExecuteReader(command))
            {
                while (dr.Read())
                {
                    areas.Add(new Area
                    {
                        Descripcion = dr.GetString(dr.GetOrdinal("Descripcion")),
                        IdArea = dr.GetInt32(dr.GetOrdinal("IdArea")),
                        IdAreaSuperior = dr.IsDBNull(dr.GetOrdinal("IdAreaSuperior")) ? byte.MinValue : dr.GetInt32(dr.GetOrdinal("IdAreaSuperior")),
                        Nombre = dr.GetString(dr.GetOrdinal("Nombre")),
                        Sucursal = SucursalData.Instancia.ObtenerPorID(dr.GetInt32(dr.GetOrdinal("IdSucursal")))
                    });
                }
            }
            return areas;
        }

        public Area ObtenerPorID(int areaID)
        {
            Area area = null;
            DbCommand command = db.GetStoredProcCommand("GetAreaByIdArea");
            db.AddInParameter(command, "idArea", DbType.Int32, areaID);

            IDataReader dr = db.ExecuteReader(command);
            while (dr.Read())
            {
                area = new Area
                {
                    Descripcion = dr.GetString(dr.GetOrdinal("Descripcion")),
                    IdArea = dr.GetInt32(dr.GetOrdinal("IdArea")),
                    IdAreaSuperior = dr.IsDBNull(dr.GetOrdinal("IdAreaSuperior")) ? byte.MinValue : dr.GetInt32(dr.GetOrdinal("IdAreaSuperior")),
                    Nombre = dr.GetString(dr.GetOrdinal("Nombre")),
                    Sucursal = SucursalData.Instancia.ObtenerPorID(dr.GetInt32(dr.GetOrdinal("IdSucursal")))
                };
            }
            return area;
        }

        public bool Agregar(Area area)
        {
            DbCommand command = db.GetStoredProcCommand("InsertArea");
            db.AddInParameter(command, "descripcion", DbType.String, area.Descripcion);
            db.AddOutParameter(command, "idArea", DbType.Int32, 11);
            db.AddInParameter(command, "idAreaSuperior", DbType.Int32, area.IdAreaSuperior);
            db.AddInParameter(command, "idSucursal", DbType.Int32, area.Sucursal.IdSucursal);
            db.AddInParameter(command, "nombre", DbType.String, area.Nombre);

            int result = db.ExecuteNonQuery(command);

            if (result != 0) return true;
            else return false;
        }

        public bool Modificar(Area area)
        {
            DbCommand command = db.GetStoredProcCommand("UpdateArea");
            db.AddInParameter(command, "descripcion", DbType.String, area.Descripcion);
            db.AddInParameter(command, "idArea", DbType.Int32, area.IdArea);
            db.AddInParameter(command, "idAreaSuperior", DbType.Int32, area.IdAreaSuperior);
            db.AddInParameter(command, "idSucursal", DbType.Int32, area.Sucursal.IdSucursal);
            db.AddInParameter(command, "nombre", DbType.String, area.Nombre);

            int result = db.ExecuteNonQuery(command);

            if (result != 0) return true;
            else return false;
        }

        public void Eliminar(int areaID)
        {
            DbCommand command = db.GetStoredProcCommand("DeleteArea");
            db.AddInParameter(command, "idArea", DbType.Int32, areaID);
            db.ExecuteNonQuery(command);
        }
    }
}
